import json

import pymysql as db


class Insert(object):
    def __init__(self):
        self.con = db.connect(user="root", passwd="root", host="localhost", db="wangdu", charset="utf8")
        self.cur = self.con.cursor()
        self.cur.execute('use wangdu')

        # self.cur.execute('drop table douban_mv')
        # self.cur.execute(
        #     u"create table douban_mv(id int auto_increment primary key,title varchar(50),rate varchar(20),url varchar(100),cover varchar(150),directors varchar(200),film_length varchar(20),info varchar(500), imdb varchar(100), indent varchar(500))")

    def insert_item(self, item):
        directors = ''
        for i in item.get('directors'):
            directors += i + '#'
        item['directors'] = directors

        # sql = "insert into douban(id,title,rate,url,cover,directors,film_length,info,imdb, indent)values(NULL,'{}','{}','{}','{}','{}','{}','{}','{}','{}');".format(
        sql = "insert into douban(id,title,rate,url,cover,directors,film_length,info,imdb, indent)values(NULL,{},{},{},{},{},{},{},{},{});".format(
            item.get('title', ''), item.get('rate', ''), item.get('url', ''), item.get('cover', ''),
            item.get('directors', ''),
            item.get('film_length', ''), item.get('info', ''), item.get('imdb', ''), item.get('indent', ''))
        print('sql', sql)
        try:
            a = self.cur.execute(sql)
            print(a)
        except Exception as e:
            print('exception : ', str(e))
        self.con.commit()
        self.con.close()
        return True


if '__main__' == __name__:
    import os

    abs_path = os.path.abspath('.')

    _insert = Insert()
    for item in json.load(open(abs_path + '/douban/items.json', 'r', encoding='utf-8')):
        print(item)
        if _insert.insert_item(item):
            print('success')
        else:
            print('error', item.get('title'))

        print('--' * 20)
